{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取\"恋爱集合.xlsx\" pandas\n",
    "import pandas as pd\n",
    "import os\n",
    "from functools import lru_cache\n",
    "\n",
    "@lru_cache(maxsize=None)\n",
    "def load_and_process_data(file_path):\n",
    "    # 读取Excel文件\n",
    "    df = pd.read_excel(file_path)\n",
    "\n",
    "    # 把充值时间转换成日期格式\n",
    "    df['充值时间'] = pd.to_datetime(df['充值时间'])\n",
    "\n",
    "    # 定义一个函数来创建时间区间\n",
    "    def create_time_interval(time):\n",
    "        minutes = time.hour * 60 + time.minute\n",
    "        interval_start = (minutes // 5) * 5\n",
    "        interval_end = interval_start + 5\n",
    "        return f\"{interval_start//60:02d}:{interval_start%60:02d}-{interval_end//60:02d}:{interval_end%60:02d}\"\n",
    "\n",
    "    # 创建新列 '时间区间'\n",
    "    df['时间区间'] = df['充值时间'].dt.time.apply(create_time_interval)\n",
    "\n",
    "    # 根据时间区间进行分组，计算每个区间的实际金额总和\n",
    "    grouped = df.groupby(\"时间区间\")[\"实际金额\"].sum().reset_index()\n",
    "\n",
    "    # 添加累加金额列\n",
    "    grouped[\"累加金额\"] = grouped[\"实际金额\"].cumsum()\n",
    "\n",
    "    # 计算总实际金额\n",
    "    总实际金额 = grouped[\"实际金额\"].sum()\n",
    "\n",
    "    # 添加累加占比列\n",
    "    grouped[\"累加占比\"] = grouped[\"累加金额\"] / 总实际金额\n",
    "\n",
    "    return grouped\n",
    "\n",
    "def predict_daily_sales(current_sales_amount,app_name=\"恋爱集合\"):\n",
    "    file_path = f\"{app_name}.xlsx\"\n",
    "    # 使用缓存加载和处理数据\n",
    "    grouped = load_and_process_data(file_path)\n",
    "\n",
    "    # 获取当前时间\n",
    "    current_time = pd.Timestamp.now().time()\n",
    "    # 筛选出 < current_time 的时间区间\n",
    "    current_interval = grouped[\n",
    "        grouped[\"时间区间\"].apply(\n",
    "            lambda x: pd.to_datetime(f'{x.split(\"-\")[0]}', format=\"%H:%M\").time()\n",
    "            <= current_time\n",
    "        )\n",
    "    ]\n",
    "    # 得出最后一个累积占比的值\n",
    "    last_cumulative_ratio = current_interval[\"累加占比\"].iloc[-1]\n",
    "\n",
    "    # 推测出当前的实际销售额\n",
    "    predicted_daily_sales = current_sales_amount / last_cumulative_ratio\n",
    "\n",
    "    return predicted_daily_sales\n",
    "\n",
    "\n",
    "# 如果需要清除缓存（例如，文件已更新），可以使用：\n",
    "# load_and_process_data.cache_clear()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测今日总销售额：22611.14\n"
     ]
    }
   ],
   "source": [
    "# 使用示例\n",
    "# 当前的销售金额\n",
    "current_sales = 7000\n",
    "predicted_sales = predict_daily_sales(current_sales,app_name=\"恋爱集合\")\n",
    "print(f\"预测今日总销售额：{predicted_sales:.2f}\")\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "crewai",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
